package com.df.springmvc.system.role.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.common.bean.SQLInsertBean;
import com.common.bean.SQLUpdateBean;
import com.df.springmvc.supper.SupperDao;


@Repository
public class RoleDao extends SupperDao{
	private String message;
	
	/**
	 * *
	 * @param sql
	 *            查询的SQL
	 * @param param_HashMap
	 *            查询参数
	 * @param i
	 *            从第几天开始
	 * @param j
	 *            显示多少条数据
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<Object> query(HashMap<String,String> map,Object[] attribute,int i,int j) throws Exception{
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select '' as 'ck',guid,roleCode,roleName,description,(select v.userGroupName from  v_usergroup_roleToGroupName v where v.roleCode =ro.roleCode ) as UserGroupName from s_role_info ro where 1=1 and status = 1 ";
		if(map.get("roleName") != null && !"".equals(map.get("roleName"))){
			sql=sql + " and roleName LIKE :s  ";
		}
		sql = sql+" order by createTime desc";
		SQLQuery query=session.createSQLQuery(sql);
		if(map.get("roleName") != null && !"".equals(map.get("roleName"))){
			query.setParameter("s","%" + map.get("roleName") + "%");
		}
		query.setFirstResult(i);
		query.setMaxResults(j);
		return (ArrayList<Object>)query.list();
	}
	/**
	 * *
	 * 添加的时候验证重复
	 * @param map
	 * @return
	 */
	public Object queryVerify(HashMap<String,String> map){
		Session session=getSessionFactory().getCurrentSession();
		String sql="select 0 from s_role_info where roleName=:roleName and STATUS = 1";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("roleName",map.get("roleName"));
		if(query.list().size() > 0){
			message="有重复的角色名称，请重新输入！";
			return message;
		}else{
			Session session2=getSessionFactory().getCurrentSession();
			String sql2="select 0 from s_role_info where roleCode=:roleCode and STATUS = 1";
			SQLQuery query2=session2.createSQLQuery(sql2);
			query2.setString("roleCode",map.get("roleCode"));
			if(query2.list().size() > 0){
				message="有重复的角色编号，请重新输入！";
				return message;
			}
		}
		return null;
	}
	/**
	 * 修改的时候验重
	 * @param map
	 * @return
	 */
	public Object queryVerifyUpd(HashMap<String,String> map){
		Session session=getSessionFactory().getCurrentSession();
		String sql="select 0 from s_role_info where roleName=:roleName and STATUS = 1 and guid not in (:gu)";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("roleName",map.get("roleName"));
		query.setString("gu",map.get("guid"));
		if(query.list().size() > 0){
			message="有重复的角色名称，请重新输入！";
			return message;
		}
		return null;
	}
	
	public Object query(HashMap<String,String> map){
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select * from s_users_info where 1=1 and STATUS = 1 and guid=:g";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("g",map.get("guid"));
		return query.list();
	}
	@Override
	public Object add(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		SQLInsertBean insertBean=new SQLInsertBean("s_role_info");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object o=excuteSqlBean(insertBean);
		return o;
	}
	/**
	 * 关联用户组，添加中间表
	 * @param map
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public Object addGroup(HashMap<String, String> map,ArrayList<String[]> list) throws Exception {
		// TODO Auto-generated method stub
		String result = "fail";
		int num=0;
		String roleCode = (String) map.get("roleCode");
		ArrayList<Object> listQuery = (ArrayList<Object>) VerifyUserGroup(roleCode);
		int len = listQuery.size();
		if (len == 0) {
			result = "success";
		} else {
			Object o = delGroup(roleCode);
			if ((len + "").equals(o)) {
				result = "success";
			}
		}
		if (result == "success") {
			if(list.size()!=0){
				String[] arr = { "guid", "status", "createBy", "createTime","roleCode","userGroupCode"};
				for (String[] s : list) {
					HashMap<String, Object> hashMap = new HashMap<String, Object>();
					for (int i = 0; i < arr.length; i++) {
						hashMap.put(arr[i], s[i]);
					}
					Object o = addGroup2(hashMap);
					if ("1".equals(o)) {
						num++;
					} else {
						break;
					}
				}
			}else{
				num=1;
			}
		}else{
			throw new RuntimeException("关联失败！");
		}
		return num;
		
	}
	@Override
	public Object upd(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		Object rs=excuteSqlBean(new SQLUpdateBean("s_role_info",map,tmp){});
		return rs;
	}
	@Override
	public HashMap<String,Object> view(String guid) throws Exception{
		// TODO Auto-generated method stub
		String sql="select * from s_role_info where guid=:guid";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",guid);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 1){
			return list.get(0);
		}else{
			throw new Exception("找不到数据");
		}
	}
	@Override
	public Object del(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		String result = "fail";
		String roleCode = (String) map.get("roleCode");
		map.remove("roleCode");
		HashMap<String, Object> tmp = new HashMap<String, Object>();
		tmp.put("guid", map.get("guid"));
		Object rs = excuteSqlBean(new SQLUpdateBean("s_role_info", map, tmp) {});
		if("1".equals(rs)){
			ArrayList<Object> listQuery = (ArrayList<Object>) VerifyUserGroup(roleCode);
			int len = listQuery.size();
			if (len == 0) {
				result = "success";
			} else {
				Object o = delGroup(roleCode);
				if ((len + "").equals(o)) {
					result = "success";
				}
			}
		}
		if(result=="success"){
			return rs;
		}else{
			throw new RuntimeException("删除失败");
		}
	}
	/**
	 * 中间表单条数据添加
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Object addGroup2(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		SQLInsertBean insertBean=new SQLInsertBean("sr_userGroup_role");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object resulto=excuteSqlBean(insertBean);
		return resulto;
	}
	/**
	 * 删除中间表的数据
	 * @param roleCode 角色ID
	 * @return
	 * @throws Exception
	 */
	public Object delGroup(String roleCode) throws Exception {
		// TODO Auto-generated method stub
		String sql1 = "delete from sr_userGroup_role where roleCode = ?";
		Object o = updateSql(sql1, new Object[] { roleCode });
		return o;
	}
	public boolean check(String guid) throws Exception {
		// TODO Auto-generated method stub
		String sql="select * from sr_userGroup_role where guid=:guid";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",guid);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 0){
			return true;
		}else{
			return false;
		}
	}
	public String getMessage(){
		return message;
	}
	public void setMessage(String message){
		this.message=message;
	}
	/**
	 * 查询中间表已经存在的数据
	 * @param roleCode
	 * @return
	 */
	public List VerifyUserGroup(String roleCode) {
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select guid from sr_userGroup_role where roleCode=:roleCode and STATUS = 1 ";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("roleCode",roleCode);
		return query.list();
	}
	
	/**
	 * 获取未被关联的用户组信息
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getUserGroup(String roleCode) throws Exception {
		Session session = getSessionFactory().getCurrentSession();
		String sqlStaff = "select userGroupCode from sr_userGroup_role where 1=1 and status=1 and roleCode='"+roleCode+"'";
		// ArrayList<String> listStaff = (ArrayList<String>)querySql(sqlStaff);
		SQLQuery query = session.createSQLQuery(sqlStaff);
		ArrayList<String> listStaff = (ArrayList<String>) query.list();
		String ucode = "";
		String u = "";
		for (int i = 0; i < listStaff.size(); i++) {
			if (i == listStaff.size() - 1) {
				u = "'" + (String) listStaff.get(i) + "'";
			} else {
				u = "'" + (String) listStaff.get(i) + "'" + ",";
			}
			ucode += u;
		}
		String sql = "";
		if ("".equals(ucode)) {
			sql = "select userGroupCode,userGroupName from s_userGroup_info where 1=1 and status=1 ";
		} else {
			sql = "select userGroupCode,userGroupName from s_userGroup_info where 1=1 and status=1  and userGroupCode not in("+ ucode + ")";
		}
		return querySqlToSQLResultBean(sql).getMapList();
	}
	/**
	 * 该角色已关联的用户组
	 * @param roleCode
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getUserTeamGroup(String roleCode) throws Exception {
		// TODO Auto-generated method stub
		String sql = "select s.userGroupCode,b.userGroupName from sr_userGroup_role s left join  s_userGroup_info b on  b.userGroupCode=s.userGroupCode  where 1=1 and s.status=1 and s.roleCode='"+roleCode+"'";
		return querySqlToSQLResultBean(sql).getMapList();
		
	}
	/**
	 * 根据用户编号获取用户的角色信息
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public  ArrayList<HashMap<String ,Object>> queryLoginUserRoleInfo(String userAccount) throws Exception{
		String sql = "select r.roleCode,r.roleName,ug.userGroupCode,ug.userGroupName,u.userAccount from s_role_info r INNER JOIN sr_usergroup_role ru on ru.roleCode = r.roleCode INNER JOIN s_usergroup_info ug on  ug.userGroupCode = ru.userGroupCode INNER JOIN sr_user_group sug ON sug.userGroupCode = ug.userGroupCode INNER JOIN s_users_info u on u.userAccount = sug.userAccount  where u.userAccount = '"+userAccount+"' and u.status = '1'";
		return querySqlToSQLResultBean(sql).getMapList();
	}
	
	
}
